import requests
from bs4 import BeautifulSoup as bs
import json
DEFAULT_USER_AGENT = "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36"

headers = {'user-agent': DEFAULT_USER_AGENT}
url = 'https://list.jd.com/list.html?cat=737,794,12526'
data_list = []
#请求响应
response = requests.get(url=url,headers=headers)
#解析HTML
bs_obj = bs(response.text,'html.parser')
#选中所有商品列表
box_list = bs_obj.select('li.gl-item')
for box in box_list:
	#图片
	img_url = box.select('a > img')
	#商品名称
	goods_title = box.select("a  > em")[0].text.strip()
	#商品详情url
	goods_title_url = box.select('a')[1].attrs['href']
	#商品价格标识
	sku = goods_title_url.split('//item.jd.com/')[1].split('.')[0]
	#商品价格接口
	price_url = 'https://p.3.cn/prices/mgets?skuIds=J_'+sku
	price_r = requests.get(url=price_url,headers=headers)
	#商品价格
	price = json.loads(price_r.text)[0]['p']
	#图片采用懒加载方式，前面几条有src属性，后面几条是自定义data-lazy-img属性
	if img_url[0].has_attr('src'):
		img_url = img_url[0].attrs['src']
	else:
		img_url = img_url[0]['data-lazy-img']
	data_list.append({
		'sku_id':sku,
		'goods_title':goods_title,
		'goods_title_url':goods_title_url,
		'img_url':img_url,
		'price':price,
		})

# data = [list(val.values()) for val in data_list]
# #写入文件
# with open('C:/Users/Administrator/2.txt','w',encoding='utf-8') as f:
# 	f.write(str(data))


import pymysql 
DB_IP = '127.0.0.1'
DB_PORT = 3306
DB_USER = 'root'
DB_PWD = '123456'

class MySqlHelper(object):
	def __init__(self):
		self.connect = pymysql.Connect(host=DB_IP, port=DB_PORT, user=DB_USER, password=DB_PWD, database='jd_sku')
		cur = self.connect.cursor()
		if not cur:
			raise (NameError, "connect db fail")
		self.cursor = cur

	def exec_many(self,sql,param_list):
		if not self.connect.open:
			self.__init__()
		self.cursor.executemany(sql,param_list)
		self.connect.commit()
		self.connect.close()


if __name__ == '__main__':
	mysql = MySqlHelper()
	sql = "INSERT INTO jd_item(sku_id,goods_title_url,goods_title,img_url,price) values(%(sku_id)s,%(goods_title_url)s,%(goods_title)s,%(img_url)s,%(price)s)"

	data = [val for val in data_list]
	mysql.exec_many(sql,data)

